<?php
class Advertisements extends Dao
{
	private static $_instance = null;
	protected $_db = null, $_cache = null;
	
	protected function __construct()
	{
		parent::__construct();
	}
	
	public static function getInstance() 
    {
        if (null === self::$_instance) {
            self::$_instance = new self();
        }
        return self::$_instance;
    }
	
	public function getListClients($intPage = 1, $intLimit = MIN_ROWS_PER_PAGE)
	{
		try{
			$intStart = ($intPage-1)*$intLimit;
			$strSql = " SELECT SQL_CALC_FOUND_ROWS *
						FROM tbl_adv_clients
						LIMIT :intStart, :intLimit";
			
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":intStart",$intStart,PDO::PARAM_INT);
			$cmd->bindParam(":intLimit",$intLimit,PDO::PARAM_INT);
			$arrResults = $cmd->queryAll();
			if(!empty($arrResults)){
				$arrResults[0]['total_rows'] = $this->_db->createCommand('SELECT FOUND_ROWS();')->queryScalar();
			}
			return $arrResults;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getClient($id)
	{
		try{
			$strSql = " SELECT *
						FROM tbl_adv_clients
						WHERE client_id=:id";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$id,PDO::PARAM_STR);
			return $arrResults = $cmd->queryRow();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function insertClient(array $args)
	{
		try{
			$defaults = array(
				'client_id' => null,
				'client_name' => null,
				'client_email' => null,
				'client_address' => null,
				'client_phone' => null,
				'client_description' => null,
				'client_created_date' =>  new CDbExpression('NOW()')
			);
			$args = $this->args_merge($defaults, $args);
			
			$cmd = $this->_db->createCommand();
			$cmd->insert('tbl_adv_clients',$args);
			return Yii::app()->db->getLastInsertId();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function updateClient($id, array $args)
	{
		try{
			$defaults = array(
				'client_name' => null,
				'client_email' => null,
				'client_address' => null,
				'client_phone' => null,
				'client_description' => null,
				'client_last_modified_date' =>  new CDbExpression('NOW()')
			);
			$args = $this->args_merge($defaults, $args);
			
			$cmd = $this->_db->createCommand();
			return $cmd->update('tbl_adv_clients',$args, 'client_id=:id', array(':id'=>$id));
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function deleteClient($id)
	{
		try{
			$cmd = $this->_db->createCommand();
			//delete from campaign
			//delete client
			return $cmd->delete('tbl_adv_clients', 'client_id=:id', array(':id'=>$id));			
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getListZones(array $args = array(), $intPage = 1, $intLimit = MIN_ROWS_PER_PAGE)
	{
		try{
			$defaults = array(
				
			);
			$args = $this->args_merge($defaults, $args);
			
			$intStart = ($intPage-1)*$intLimit;

			$strSql = " SELECT SQL_CALC_FOUND_ROWS *
						FROM tbl_adv_zones
						LIMIT :intStart, :intLimit";
			
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":intStart",$intStart,PDO::PARAM_INT);
			$cmd->bindParam(":intLimit",$intLimit,PDO::PARAM_INT);
			
			$arrResults = $cmd->queryAll();
			if(!empty($arrResults)){
				$arrResults[0]['total_rows'] = $this->_db->createCommand('SELECT FOUND_ROWS();')->queryScalar();
			}
			return $arrResults;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getZone($id)
	{
		try{
			$strSql = " SELECT *
						FROM tbl_adv_zones
						WHERE zone_id=:id";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$id,PDO::PARAM_STR);
			return $arrResults = $cmd->queryRow();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function insertZone(array $args)
	{
		try{
			$defaults = array(
				'zone_name' => null,
				'zone_description' => null,
				'zone_created_date' => new CDbExpression('NOW()'),
				'zone_creator_id' => null ,
				'zone_width' => null,
				'zone_height' => null,
				'zone_type' => null,
				'zone_status' => null,
			);
			$args = $this->args_merge($defaults, $args);
			$cmd = $this->_db->createCommand();
			$cmd->insert('tbl_adv_zones',$args);
			return Yii::app()->db->getLastInsertId();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function updateZone($id, array $args)
	{
		try{
			$defaults = array(
				'zone_name' => null,
				'zone_description' => null,
				'zone_last_modified_date' => new CDbExpression('NOW()'),
				'zone_last_modifier_id' => null,
				'zone_width' => null,
				'zone_height' => null,
				'zone_type' => null,
				'zone_status' => null,
			);
			$args = $this->args_merge($defaults, $args);
			
			$cmd = $this->_db->createCommand();
			return $cmd->update('tbl_adv_zones',$args, 'zone_id=:id', array(':id'=>$id));
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function deleteZone($id)
	{
		try{
			$cmd = $this->_db->createCommand();
			//delete from banners
			$cmd->delete('tbl_adv_banner_zone', 'zone_id=:id', array(':id'=>$id));
			//delete zone
			return $cmd->delete('tbl_adv_zones', 'zone_id=:id', array(':id'=>$id));			
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getListCampaigns(array $args = array(), $intPage = 1, $intLimit = MIN_ROWS_PER_PAGE)
	{
		try{
			$defaults = array(
				'client_id' => null
			);
			$args = $this->args_merge($defaults, $args);
			
			$intStart = ($intPage-1)*$intLimit;

			$strSql = " SELECT SQL_CALC_FOUND_ROWS *
						FROM tbl_adv_campaigns
						WHERE client_id = IFNULL(:client, client_id)
						LIMIT :intStart, :intLimit";
			
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":client",$args['client_id'],PDO::PARAM_INT);
			$cmd->bindParam(":intStart",$intStart,PDO::PARAM_INT);
			$cmd->bindParam(":intLimit",$intLimit,PDO::PARAM_INT);
			
			$arrResults = $cmd->queryAll();
			if(!empty($arrResults)){
				$arrResults[0]['total_rows'] = $this->_db->createCommand('SELECT FOUND_ROWS();')->queryScalar();
			}
			return $arrResults;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getCampaign($id)
	{
		try{
			$strSql = " SELECT *
						FROM tbl_adv_campaigns
						WHERE campaign_id=:id";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$id,PDO::PARAM_STR);
			return $arrResults = $cmd->queryRow();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function insertCampaign(array $args)
	{
		try{
			$defaults = array(
				'client_id' => null,
				'campaign_name' => null,
				'campaign_description' => null,
				'campaign_created_date' => new CDbExpression('NOW()'),
				'campaign_creator_id' => null,
				'campaign_published_date' => null,
				'campaign_expiry_date' => null,
				'campaign_target_impression' => 0,
				'campaign_target_click' => 0,
				'campaign_target_converstion' => 0,
				'campaign_type' => 0,
				'campaign_status' => null
			);
			$args = $this->args_merge($defaults, $args);
			$cmd = $this->_db->createCommand();
			$cmd->insert('tbl_adv_campaigns',$args);
			return Yii::app()->db->getLastInsertId();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function updateCampaign($id, array $args)
	{
		try{
			$defaults = array(
				'client_id' => null,
				'campaign_name' => null,
				'campaign_description' => null,
				'campaign_last_modified_date' => new CDbExpression('NOW()'),
				'campaign_last_modifier_id' => null,
				'campaign_published_date' => null,
				'campaign_expiry_date' => null,
				'campaign_target_impression' => 0,
				'campaign_target_click' => 0,
				'campaign_target_converstion' => 0,
				'campaign_type' => 0,
				'campaign_status' => null
			);
			$args = $this->args_merge($defaults, $args);
			
			$cmd = $this->_db->createCommand();
			return $cmd->update('tbl_adv_campaigns',$args, 'campaign_id=:id', array(':id'=>$id));
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function deleteCampaign($id)
	{
		try{
			$cmd = $this->_db->createCommand();
			//delete from banners
			//$cmd->delete('tbl_adv_banner_zone', 'zone_id=:id', array(':id'=>$id));
			//delete campaigns
			return $cmd->delete('tbl_adv_campaigns', 'campaign_id=:id', array(':id'=>$id));			
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getListBanners(array $args = array(), $intPage = 1, $intLimit = MIN_ROWS_PER_PAGE)
	{
		try{
			$defaults = array(
				'campaign_id' => null,
				'zone_id' => null
			);
			$args = $this->args_merge($defaults, $args);
			
			$intStart = ($intPage-1)*$intLimit;
			
			$strZoneCond = "";
			if(!empty($args['zone_id'])){
				$strZoneCond = "INNER JOIN (SELECT banner_id
											FROM tbl_adv_banner_zone
											WHERE zone_id IN ( :zone )
								) bc ON b.banner_id = bc.banner_id";
			}

			$strSql = " SELECT SQL_CALC_FOUND_ROWS *
						FROM tbl_adv_banners b {$strZoneCond}
						WHERE b.campaign_id = IFNULL(:campaign, campaign_id)
						LIMIT :intStart, :intLimit";
			
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":campaign",$args['campaign_id'],PDO::PARAM_INT);
			if(!empty($args['zone_id']))
				$cmd->bindParam(":zone",$args['zone_id'],PDO::PARAM_INT);
			$cmd->bindParam(":intStart",$intStart,PDO::PARAM_INT);
			$cmd->bindParam(":intLimit",$intLimit,PDO::PARAM_INT);
			
			$arrResults = $cmd->queryAll();
			if(!empty($arrResults)){
				$arrResults[0]['total_rows'] = $this->_db->createCommand('SELECT FOUND_ROWS();')->queryScalar();
			}
			return $arrResults;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
		
	public function getBanner($id)
	{
		try{
			$strSql = " SELECT *, (SELECT GROUP_CONCAT(zone_id) FROM tbl_adv_banner_zone WHERE banner_id = b.banner_id) as zones
						FROM tbl_adv_banners b
						WHERE b.banner_id=:id";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$id,PDO::PARAM_STR);
			$arrResults = $cmd->queryRow();
			if(isset($arrResults['zones'])){
				$arrResults['zones'] = explode(',', $arrResults['zones']);
			}
			return $arrResults;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function insertBanner(array $args)
	{		
		try{
			$defaults = array(
				'campaign_id' => null,
				'banner_name' => null,
				'banner_description' => null,
				'banner_link' => null,
				'banner_linktarget' => null,
				'banner_file' => null,
				'banner_file_extension' => null,
				'banner_alt' => null,
				'banner_created_date' =>  new CDbExpression('NOW()'),
				'banner_status' => null,
			);
			$args = $this->args_merge($defaults, $args);
			
			$cmd = $this->_db->createCommand();
			$cmd->insert('tbl_adv_banners',$args);
			return Yii::app()->db->getLastInsertId();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function updateBanner($id, array $args)
	{		
		try{
			$defaults = array(
				'campaign_id' => null,
				'banner_name' => null,
				'banner_description' => null,
				'banner_link' => null,
				'banner_linktarget' => null,
				'banner_file' => null,
				'banner_file_extension' => null,
				'banner_alt' => null,
				'banner_last_modified_date' => new CDbExpression('NOW()'),
				'banner_status' => null,
			);
			$args = $this->args_merge($defaults, $args);
			
			$cmd = $this->_db->createCommand();
			return $cmd->update('tbl_adv_banners',$args, 'banner_id=:id', array(':id'=>$id));
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function insertBannerZone($bannerId, $zoneId)
	{
		if(empty($bannerId) || empty($zoneId)) throw new Exception('banner and zone can not null');
		try{
			$args = array(
				'banner_id' => $bannerId,
				'zone_id' => $zoneId
			);
			return $this->_db->createCommand()->insert('tbl_adv_banner_zone', $args);
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function deleteBannerZone($bannerId, $zoneId)
	{
		if(empty($bannerId) && empty($zoneId)) throw new Exception('banner and zone can not null');
		try{
			return $this->_db->createCommand()->delete('tbl_adv_banner_zone', 'banner_id = IFNULL(:bid,banner_id) AND zone_id=IFNULL(:zid,zone_id)', array(':bid'=>$bannerId, ':zid'=>$zoneId));
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function deleteBanner($id){
		try{
			$cmd = $this->_db->createCommand();
			return $cmd->delete('tbl_adv_banners', 'banner_id=:id', array(':id'=>$id));			
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
}